import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from typing import Optional

from langchain_core.tools import tool
from tools.get_mysql import MySQLConnectionFactory
import random
import datetime
from tools.utility import timer_decorator

mysql_factory = MySQLConnectionFactory()

@tool
@timer_decorator
def recommend_product(category: Optional[str] = None, min_price: Optional[float] = 0.1, max_price: Optional[float] = 999999.99) -> list[dict]:
    """按照客户需求推荐商品。
    Args:
        category (Optional[str]): 商品类别,例如"手机""服装""美妆护肤"等
        min_price (Optional[float]): 最低价格
        max_price (Optional[float]): 最高价格
        
    Returns:
        list[dict]: 包含以下字段的商品信息列表:
            - item_id (int): 商品ID
            - product_name (str): 商品名称
            - product_description (str): 商品描述
            - brand (str): 商品品牌
            - category (str): 商品类别
            - price (float): 商品价格
            - is_listed (bool): 是否上架
            - listing_date (str): 上架日期
            - promotion_info (str): 优惠信息
            - stock_quantity (int): 库存数量
            - min_stock_alert (int): 最低库存预警值
            - weight (float): 重量
            - dimensions (str): 尺寸
            - sales_volume (int): 销量
            - rating (float): 评分
            - review_count (int): 评价数量
    """
    query = """
    SELECT * FROM products WHERE is_listed = 1 AND category = %s AND price >= %s AND price <= %s limit 3;
    """
    results = mysql_factory.execute_query(query, (category, min_price, max_price))
    return results



@tool
@timer_decorator
def search_product(item_id: int) -> list[dict]:
    """获取某个商品的所有信息。
    Args:
        item_id (str): 商品的唯一标识ID,例如10243


    Returns:
        list[dict]: 包含以下字段的商品信息列表:
            - item_id (int): 商品ID
            - product_name (str): 商品名称
            - product_description (str): 商品描述
            - brand (str): 商品品牌
            - category (str): 商品类别
            - price (float): 商品价格
            - is_listed (bool): 是否上架
            - listing_date (str): 上架日期
            - promotion_info (str): 优惠信息
            - stock_quantity (int): 库存数量
            - min_stock_alert (int): 最低库存预警值
            - weight (float): 重量
            - dimensions (str): 尺寸
            - sales_volume (int): 销量
            - rating (float): 评分
            - review_count (int): 评价数量

    Raises:
        ValueError: 当没有提供passenger_id时抛出
    """

    if not item_id:
        raise ValueError("No item ID configured.")

    query = """
    SELECT * FROM products WHERE item_id = %s
    """
    results = mysql_factory.execute_query(query, (item_id,))

    return results

@tool
@timer_decorator
def purchase_product(customer_id: int,item_id: int, quantity: int) -> str:
    """购买商品。
    Args:
        item_id (int): 商品ID
        quantity (int): 购买数量
    
    Returns:
        str: 购买成功信息
    """
    if not item_id or not quantity:
        raise ValueError("No item ID or quantity configured.")
    try:
    ###查询商品价格
        product_info = search_product.invoke({"item_id": item_id})
        price = product_info[0]['price']

        ###计算总价
        total_price = price * quantity


        ###查询用户信息
        search_user = "SELECT * FROM customers WHERE customer_id = %s"
        user = mysql_factory.execute_query(search_user, (customer_id,))
        
        order_id = random.randint(10000000, 99999999)
        order_number = datetime.datetime.now().strftime("%Y%m%d%H%M%S")+str(order_id)
        order_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        tracking_number = random.choice(['SF','JD','YT','YZ'])+str(random.randint(100000000000, 999999999999))
        query = f"""

        INSERT INTO orders (order_id, order_number, order_status, order_time,
        customer_id, customer_name, customer_contact, item_id, product_name,
        product_quantity, product_price, total_amount, discount_amount, paid_amount, 
        shipping_address, shipping_method, tracking_number, payment_method, payment_time,
        payment_status)
        VALUES 
        (
        {order_id},
        {order_number},
        '已付款',
        '{order_time}',
        {customer_id},
        '{user[0]['username']}',
        '{user[0]['phone_number']}',
        {item_id},
        '{product_info[0]['product_name']}',
        {quantity},
        {price},
        {total_price},
        0,
        {total_price},
        '{user[0]['address']}',
        '快递',
        '{tracking_number}',
        '支付宝',
        '{order_time}',
        '已支付'
                  )
        """


        mysql_factory.execute_insert(query)
        return "购买成功!"
    except Exception as e:
        return f"购买失败: {e}"


product_safe_tools = [recommend_product,search_product]
product_sensitive_tools = [purchase_product]


if __name__ == "__main__":
    # print(search_product.invoke({"item_id": 10243}))
    # print(purchase_product.invoke({"customer_id": 1, "item_id": 10243, "quantity": 1}))
    print(recommend_product.invoke({"category": "手机", "min_price": 5000, "max_price": 8000}))
